<?php
    /**
    **  Database Connection
    **/
    $con = mysql_connect("oege.ie.hva.nl","keppel08","1wptP1sD@");
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("zkeppel08", $con);

    //Class for data
    class gr_serie
    {
        public $name = "";
        public $color = "";
        public $data = array();
    }

    class gr_serie_bar
    {
        public $name = "";
        public $data = array();
    }

    class gr_source
    {
        public $city = "";
        public $description = "";
        public $link_text = "";
        public $link = "";
        public $category = "";
    }

    class gr_source_category
    {
        public $category = "";
        public $sources = array();
    }

    //Get all values for a specific category of a specific city
    function getValues()
    {
        //Get/set all send values
        $city = $_REQUEST['city'];
        $category = $_REQUEST['category'];

        //Search for the values belonging to the city and category provided
        $getValues = mysql_query("SELECT DISTINCT AV_VALUES.year, AV_VALUES.value, AV_VALUES.custom_value, AV_CATEGORIES.type_chart, AV_CATEGORIES.title, AV_CITIES.citynicename, AV_CATEGORIES.has_subcategories, AV_VALUES.subcatid, AV_CATEGORIES.description, AV_CITIES.color, AV_CITIES.cityid
            FROM  AV_VALUES
            INNER JOIN AV_CITIES ON AV_CITIES.cityid = AV_VALUES.cityid
            INNER JOIN AV_CATEGORIES ON AV_CATEGORIES.catid = AV_VALUES.catid
            WHERE AV_CITIES.cityname = '".mysql_real_escape_string($city)."'
            AND AV_CATEGORIES.short_title = '".mysql_real_escape_string($category)."'
            ORDER BY year");

        $data = '';

        //If there are values
        if(mysql_num_rows($getValues) > 0)
        {
            //Graph type
            $type = "";
            //Category, like 'Housing prices'
            $category_fancy = "";
            //City, like 'San Jose'
            $city_fancy = "";
            //Is yes when the category has subcategories
            $has_subcategories = "no";
            //Description of the visualization
            $description = "";

            $color = "";

            //Array to contain all values
            $values = array();
            //Array to contain all subcategories
            $subcategories = array();
            //Array to contain all data series
            $series = array();
            //Array to contain only the names of (sub)categories
            $clean_subcategories = array();

            $cityid = 0;

            //For every result
            while($row = mysql_fetch_array($getValues))
            {
                $type = $row["type_chart"];
                $city_fancy = $row['citynicename'];
                $category_fancy = $row['title'];
                $has_subcategories = $row['has_subcategories'];
                $description = $row['description'];
                $color = $row['color'];
                $cityid = $row['cityid'];

                array_push($values, array(
                    "year" => $row['year'],
                    "value" => $row['value'],
                    "custom_value" => $row['custom_value'],
                    "subcatid" => $row['subcatid']
                ));
            }

            $data = new stdClass();
            $data->type = $type;
            $data->title = $city_fancy." - ".$category_fancy;

            $data->categories = array();

            switch($type)
            {
                case "line":
                    $getYears = mysql_query("SELECT AV_VALUES.year
                        FROM AV_VALUES
                        INNER JOIN AV_CATEGORIES ON AV_CATEGORIES.catid = AV_VALUES.catid
                        WHERE AV_CATEGORIES.short_title = '".mysql_real_escape_string($category)."'
                        AND AV_VALUES.cityid = '".mysql_real_escape_string($cityid)."'
                        GROUP BY AV_VALUES.year");

                    if(mysql_num_rows($getYears) > 0)
                    {
                        while($row = mysql_fetch_array($getYears))
                        {
                            array_push($clean_subcategories, intval($row['year']));
                        }
                    }

                    $data->subtitle = $category_fancy;
                    $data->categories = $clean_subcategories;

                    $total = new gr_serie();
                    $total->name = $city_fancy;
                    $total->color = $color;

                    $entry = array();

                    for($y = 0; $y < count($values); $y++)
                    {
                        array_push($entry, intval($values[$y]["value"]));
                    }
                    $total->data = $entry;
                    array_push($series, $total);
                    break;



                case "bar":
                    if($has_subcategories == "yes")
                    {
                        $getSubcategories = mysql_query("SELECT AV_SUBCATEGORIES.subcattitle as title, AV_SUBCATEGORIES.id as subcatid
                            FROM  AV_SUBCATEGORIES
                            INNER JOIN AV_CATEGORIES ON AV_CATEGORIES.catid = AV_SUBCATEGORIES.catid
                            WHERE AV_CATEGORIES.short_title = '".mysql_real_escape_string($category)."'");

                        if(mysql_num_rows($getSubcategories))
                        {
                            while($row = mysql_fetch_array($getSubcategories))
                            {
                                array_push($subcategories, array("title" => $row['title'], "subcatid" => $row['subcatid']));
                            }
                        }

                        for($x = 0; $x < count($subcategories); $x++)
                        {
                            $total = new gr_serie_bar();
                            $total->name = $subcategories[$x]["title"];

                            $entry = array();

                            for($y = 0; $y < count($values); $y++)
                            {
                                if($values[$y]["subcatid"] == $subcategories[$x]["subcatid"])
                                {
                                    array_push($entry, intval($values[$y]["value"]));
                                }
                            }

                            $total->data = $entry;
                            array_push($series, $total);
                        }

                        for($x = 0; $x < count($subcategories); $x++)
                        {
                            array_push($clean_subcategories, $subcategories[$x]["title"]);
                        }

                        $data->categories = array($city_fancy);
                    }
                    else
                    {
                        $getYears = mysql_query("SELECT AV_VALUES.year
                        FROM AV_VALUES
                        INNER JOIN AV_CATEGORIES ON AV_CATEGORIES.catid = AV_VALUES.catid
                        WHERE AV_CATEGORIES.short_title = '".mysql_real_escape_string($category)."'
                        AND AV_VALUES.cityid = '".mysql_real_escape_string($cityid)."'
                        GROUP BY AV_VALUES.year");

                        if(mysql_num_rows($getYears) > 0)
                        {
                            while($row = mysql_fetch_array($getYears))
                            {
                                array_push($clean_subcategories, intval($row['year']));
                            }
                        }

                        $data->subtitle = $category_fancy;
                        $data->categories = $clean_subcategories;

                        $total = new gr_serie();
                        $total->name = $city_fancy;
                        $total->color = $color;

                        $entry = array();

                        for($y = 0; $y < count($values); $y++)
                        {
                            array_push($entry, intval($values[$y]["value"]));
                        }
                        $total->data = $entry;
                        array_push($series, $total);
                    }

                    $data->subtitle = $description;
                    break;

                case "map":
                    if(count($values) > 0)
                    {
                        $data->subtitle = $category_fancy;
                        $series = $values[0]['custom_value'];
                    }
                    else
                    {
                        $data = 'no results';
                    }
                break;
            }

            $data->series = $series;
        }
        else
        {
            $data = "no results";
        }

        echo json_encode($data);
    }

    //Get all categories for a specific city
    function getCategories()
    {
        $city = $_REQUEST['city'];
        $getCities = mysql_query("SELECT AV_CATEGORIES.short_title, AV_CATEGORIES.catid, AV_CATEGORIES.title, AV_CATEGORIES.class, AV_CATEGORIES.type_chart
            FROM  AV_VALUES
            INNER JOIN AV_CITIES ON AV_CITIES.cityid = AV_VALUES.cityid
            INNER JOIN AV_CATEGORIES ON AV_CATEGORIES.catid = AV_VALUES.catid
            WHERE AV_CITIES.cityname = '".mysql_real_escape_string($city)."'
            GROUP BY AV_VALUES.catid, AV_VALUES.cityid");

        $data = array();

        if(mysql_num_rows($getCities) > 0)
        {
            while($row = mysql_fetch_array($getCities))
            {
                array_push($data, array(
                        "short_title" => $row['short_title'],
                        "title" => $row['title'],
                        "class" => $row['class'],
                        "catid" => $row['catid'],
                        "type"  => $row['type_chart']
                  )
                );
            }
        }
        else
        {
            $data[] = 'no results';
        }

        echo json_encode($data);
    }

    //Function that returns sources of a category
    function getSources()
    {
        $city = "";
        $category = "";

        $category = $_REQUEST['category'];
        if(isset($_REQUEST['city']))
        {
            $city = $_REQUEST['city'];
        }
        else
        {
            $city = "none";
        }

        $sql = '';

        if($city == 'all' && $category == 'all')
        {
            $sql .= "SELECT AV_SOURCES.description, AV_SOURCES.link_text, AV_SOURCES.link, AV_CITIES.cityname, AV_CATEGORIES.short_title
                FROM AV_SOURCES
                INNER JOIN AV_CATEGORIES ON AV_CATEGORIES.catid = AV_SOURCES.catid
                INNER JOIN AV_CITIES ON AV_CITIES.cityid = AV_SOURCES.cityid
                ORDER BY AV_SOURCES.sourceid ASC";
        }
        else
        {
            $sql = "SELECT AV_SOURCES.description, AV_SOURCES.link_text, AV_SOURCES.link, AV_CITIES.cityname, AV_CATEGORIES.short_title
                FROM AV_SOURCES
                INNER JOIN AV_CATEGORIES ON AV_CATEGORIES.catid = AV_SOURCES.catid
                INNER JOIN AV_CITIES ON AV_CITIES.cityid = AV_SOURCES.cityid
                WHERE AV_CATEGORIES.short_title = '".mysql_real_escape_string($category)."'";

            if($city != "none")
            {
                $sql .= " AND AV_CITIES.cityname = '".mysql_real_escape_string($city)."' ";
            }

            $sql .= "ORDER BY AV_SOURCES.sourceid ASC";
        }

        $getSources = mysql_query($sql);

        $data = array();

        if(mysql_num_rows($getSources) > 0)
        {
            $source_category = new gr_source_category();
            $source_category->category = $category;

            $data[] = "succes";

            while($row = mysql_fetch_array($getSources))
            {
                $source = new gr_source();
                $source->city = $row['cityname'];
                $source->description = $row['description'];
                $source->link_text = $row['link_text'];
                $source->link = $row['link'];
                $source->category = $row['short_title'];

                array_push($source_category->sources, $source);
            }
            $data[] = $source_category;
        }
        else
        {
            $data[] = 'no results';
        }

        echo json_encode($data);
    }

    //Function that executes the proper function
    function switchFunc($kind){
        switch($kind){
            case "getCategories":
                getCategories();
                break;

            case "getValues":
                getValues();
                break;

            case "getSources":
                getSources();
                break;
        }
    }

    //Check if a function has been called
    if(isset($_REQUEST['f'])){
        switchFunc($_REQUEST['f']);
    } elseif(isset($_GET['f'])) {
        switchFunc($_GET['f']);
    } elseif(isset($_POST['f'])){
        switchFunc($_POST['f']);
    } else {
        echo json_encode("");
    }
?>